MySQL 用户管理

查询用户是否存在

mysql -e "SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = 'username')" -u root  -p123456

创建用户

MySQL 提供了以下 3 种方法创建用户。

• 使用 CREATE USER 语句创建用户
• 在 mysql.user 表中添加用户
• 使用 GRANT 语句创建用户

CREATE USER

CREATE USER <用户> 
[ IDENTIFIED BY [ PASSWORD ] 'password' ] 
[ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

以 root 用户登录数据库，运行以下命令：

create user alsritter identified by '1234';

上面的命令创建了用户 alsritter，密码是 1234。在 mysql.user 表里可以查看到新增用户的信息：

来个完整的例子

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test_admin';
GRANT ALL PRIVILEGES ON my_database.* TO 'test_user'@'localhost';
FLUSH PRIVILEGES;

参数说明

• 用户：指定创建用户账号，格式为 user_name'@'host_name。如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为 %，表示一组主机，即对所有主机开放权限。
• IDENTIFIED BY子句：用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。
• PASSWORD 'password'：PASSWORD 表示使用哈希值设置密码，该参数可选。如果密码是一个普通的字符串，则不需要使用 PASSWORD 关键字。'password' 表示用户登录时使用的密码，需要用单引号括起来。

注意：user 表中的 User 和 Host 字段区分大小写，创建用户时要指定正确的用户名称或主机名。

使用 CREATE USER 语句时应注意以下几点：

CREATE USER 语句可以不指定初始密码。但是从安全的角度来说，不推荐这种做法。 使用 CREATE USER 语句必须拥有 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限。 使用 CREATE USER 语句创建一个用户后，MySQL 会在 mysql 数据库的 user 表中添加一条新记录。 CREATE USER 语句可以同时创建多个用户，多个用户用逗号隔开。

INSERT 新建用户

可以使用 INSERT 语句将用户的信息添加到 mysql.user 表中，但必须拥有对 mysql.user 表的 INSERT 权限。通常 INSERT 语句只添加 Host、User 和 authentication_string 这 3 个字段的值。

MySQL 5.7 的 user 表中的密码字段从 Password 变成了 authentication_string，如果使用的是 MySQL 5.7 之前的版本，将 authentication_string 字段替换成 Password 即可。

使用 INSERT 语句创建用户的代码如下：

INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) 
VALUES ('hostname', 'username', PASSWORD('password'), '', '', '');

新建用户成功后，但是这时如果通过该账户登录 MySQL 服务器，不会登录成功，因为 test2 用户还没有生效。

可以使用 FLUSH 命令让用户生效，命令如下：

FLUSH PRIVILEGES;

使用以上命令可以让 MySQL 刷新系统权限相关表。执行 FLUSH 命令需要 RELOAD 权限。

修改用户

可以使用 RENAME USER 语句修改一个或多个已经存在的用户账号。

RENAME USER <旧用户> TO <新用户>

SET 修改用户的密码

在 MySQL 中，只有 root 用户可以通过更新 MySQL 数据库来更改密码。使用 root 用户登录到 MySQL 服务器后，可以使用 SET 语句来修改普通用户密码。语法格式如下：

SET PASSWORD FOR 'username'@'hostname' = PASSWORD ('newpwd');

其中，username 参数是普通用户的用户名，hostname 参数是普通用户的主机名，newpwd 是要更改的新密码。

注意：新密码必须使用 PASSWORD() 函数来加密，如果不使用 PASSWORD() 加密，也会执行成功，但是用户会无法登录。

如果是普通用户修改密码，可省略 FOR 子句来更改自己的密码。语法格式如下：

SET PASSWORD = PASSWORD('newpwd');

UPDATE 修改用户的密码

可以使用 UPDATE 语句修改 MySQL 数据库的 user 表的 authentication_string 字段，从而修改普通用户的密码。UPDATA 语句的语法如下：

UPDATE MySQL.user 
SET authentication_string = PASSWORD("newpwd") 
WHERE User = "username" AND Host = "hostname";

GRANT 修改用户密码

GRANT USAGE ON *.* TO 'user'@'hostname' IDENTIFIED BY 'newpwd';

修改用户的 Host

UPDATE mysql.user SET Host = '新主机名' WHERE User = '用户名';
-- 刷新权限
FLUSH PRIVILEGES;

修改用户的数据库权限

-- 撤销用户对其他数据库的权限：
REVOKE ALL PRIVILEGES ON *.* FROM '用户名'@'主机名';

-- 授予用户对数据库的权限：
GRANT 权限列表 ON 数据库.* TO '用户名'@'主机名';


-- 刷新权限
FLUSH PRIVILEGES;

删除用户

可以使用 DROP USER 语句删除用户，也可以直接在 mysql.user 表中删除用户以及相关权限。

DROP USER <用户1> [ , <用户2> ]…

在 DROP USER 语句的使用中，若没有明确地给出账户的主机名，则该主机名默认为 %

用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 MySQL 并不会记录是谁创建了这些对象。

查看用户权限

SELECT * FROM mysql.user;

还可以使用 SHOW GRANTS FOR 语句查看权限

SHOW GRANTS FOR 'username'@'hostname';

GRANT：用户授权

拥有 GRANT 权限的用户才可以执行 GRANT 语句，其语法格式如下：

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

参数说明

• priv_type 参数表示权限类型；
• columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
• database.table 用于指定权限的级别；
• user 参数表示用户账户，由用户名和主机名构成，格式是 'username'@'hostname'；
• IDENTIFIED BY 参数用来为用户设置密码；
• password 参数是用户的新密码。

WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：

• GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
• MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
• MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
• MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
• MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

1、创建用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username：用户名；

host：指定在哪个主机上可以登录，本机可用localhost，%通配所有远程主机；

password：用户登录密码；

2、授权：

GRANT ALL PRIVILEGES ON  *.* TO 'username'@'%' IDENTIFIED BY 'password';

格式：grant 权限 on 数据库名.表名 to 用户@登录主机 identified by "用户密码"；

*.* 代表所有权；

@ 后面是访问MySQL的客户端IP地址（或是 主机名） % 代表任意的客户端，如果填写 localhost 为本地访问（那此用户就不能远程访问该mysql数据库了）。

3、刷新权限：

FLUSH PRIVILEGES;

数据库权限

授予数据库权限时，<权限类型>可以指定为以下值：

权限名称	对应user表中的字段	说明
SELECT	Select_priv	表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT	Insert_priv	表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE	Delete_priv	表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE	Update_priv	表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES	References_priv	表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE	Create_priv	表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER	Alter_priv	表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEW	Show_view_priv	表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE	Create_routine_priv	表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE	Alter_routine_priv	表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX	Index_priv	表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP	Drop_priv	表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES	Create_tmp_table_priv	表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW	Create_view_priv	表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE	Execute_priv	表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES	Lock_tables_priv	表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES 或 SUPER	Super_priv	表示以上所有权限/超级权限

表权限

授予表权限时，<权限类型>可以指定为以下值：

权限名称	对应user表中的字段	说明
SELECT	Select_priv	授予用户可以使用 SELECT 语句进行访问特定表的权限
INSERT	Insert_priv	授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限
DELETE	Delete_priv	授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限
DROP	Drop_priv	授予用户可以删除数据表的权限
UPDATE	Update_priv	授予用户可以使用 UPDATE 语句更新特定数据表的权限
ALTER	Alter_priv	授予用户可以使用 ALTER TABLE 语句修改数据表的权限
REFERENCES	References_priv	授予用户可以创建一个外键来参照特定数据表的权限
CREATE	Create_priv	授予用户可以使用特定的名字创建一个数据表的权限
INDEX	Index_priv	授予用户可以在表上定义索引的权限
ALL 或 ALL PRIVILEGES 或 SUPER	Super_priv	所有的权限名

使用例

使用 GRANT 语句创建一个新的用户 testUser，密码为 testPwd。用户 testUser 对所有的数据有查询、插入权限，并授予 GRANT 权限。SQL 语句和执行过程如下。

GRANT SELECT,INSERT ON *.*
TO 'testUser'@'localhost'
IDENTIFIED BY 'testPwd'
WITH GRANT OPTION;

REVOKE：删除用户权限

在 MySQL 中，可以使用 REVOKE 语句删除某个用户的某些权限（此用户不会被删除），在一定程度上可以保证系统的安全性。例如，如果数据库管理员觉得某个用户不应该拥有 DELETE 权限，那么就可以删除 DELETE 权限。

REVOKE 语法和 GRANT 语句的语法格式相似，但具有相反的效果。

使用 REVOKE 语句删除权限的语法格式有两种形式，如下所示：

1、删除用户某些特定的权限，语法格式如下：

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

REVOKE 语句中的参数与 GRANT 语句的参数意思相同。其中：

• priv_type 参数表示权限的类型；
• column_list 参数表示权限作用于哪些列上，没有该参数时作用于整个表上；
• user 参数由用户名和主机名构成，格式为 username'@'hostname'。

2、删除特定用户的所有权限，语法格式如下：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

使用例

REVOKE INSERT ON *.* FROM 'testUser'@'localhost';

常用的操作用户操作

-- 创建一个用户，密码为123456
create user alsritter IDENTIFIED by '123456'

-- 修改密码（当前用户）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（指定用户）
SET PASSWORD FOR alsritter = PASSWORD('123456')

-- 重命名
RENAME USER alsritter TO alsritter2

-- ALL授全部权限(库.表，所以*.*表示全部)
-- 虽然这个用户用户已经可以用来删库跑路了，但是这个特权用户还是与root用户不同，因为它无法给其他用户授权(GRANT)
GRANT ALL PRIVILEGES ON *.* TO alsritter
-- 所以如果再勾上GRANT权限就与root用户无差别了
GRANT ALL PRIVILEGES ON *.* TO alsritter WITH GRANT OPTION

-- 查询权限
SHOW GRANT FOR alsritter

-- 撤销权限 REVOKE 哪些权限，在哪个库和表(*.*)，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM alsritter

-- 删除用户
DROP USER alsritter

数据库无法远程连接

参考资料 MySQL Error: : 'Access denied for user 'root'@'localhost'

1、改表法。可能是你的帐号不允许从远程登陆，只能在 localhost。这个时候只要在 localhost 的那台电脑，登入 mysql 后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从 "localhost" 改称 "%"

use mysql;
update user set host = '%' where user = 'root';
select host, user from user;

最后，别忘了重启 mysql 使配置生效。

sudo systemctl restart mysql

2、授权法。例如，你想 user 使用 password 从任何主机连接到 mysql 服务器的话。（但是这个方法好像新版不行）

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
-- 如果是 root
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
-- 刷新访问权限表（不要忘了）
FLUSH PRIVILEGES;

SELECT user, host FROM user;

3、找到 "/etc/mysql/mysql.conf.d/mysqld.cnf" 文件并打开，加上如下配置

可以通过这个命令检查：

show variables like '%bind%';

mysql> show variables like '%bind%';
+---------------------+---------+
| Variable_name       | Value   |
+---------------------+---------+
| bind_address        | 0.0.0.0 |
| mysqlx_bind_address | 0.0.0.0 |
+---------------------+---------+
2 rows in set (0.00 sec)

最后，别忘了重启 mysql 使配置生效。

sudo systemctl restart mysql

4、需要将 root 的 plugin 属性修改为 mysql_native_password

USE mysql;
-- UPDATE user SET plugin='mysql_native_password' WHERE User='root';
update user set plugin='mysql_native_password';
FLUSH PRIVILEGES;

-- ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
-- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';


exit;

重启

sudo systemctl restart mysql

5、最终解决办法：

找到 "/etc/mysql/mysql.conf.d/mysqld.cnf" 文件并打开，加上如下配置

skip-grant-tables

重启

sudo systemctl restart mysql

登录mysql，键入mysql –uroot –p；直接回车（Enter）